package com.hnkywl.sts.dao.xsgl;

import com.hnkywl.sts.entity.htgl.Xsht;
import com.hnkywl.sts.entity.xsgl.Jfmx;
import com.hnkywl.sts.entity.xsgl.Xsd;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by HL on 2015/9/8.
 */
@Repository
public class JfmxDao extends SimpleHibernateTemplate<Jfmx, Long> {

    public Page<Map> findJfmxList(String xsdh, String xszxh, String carNo, String bindNo, Long xshtId, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = "select jfmx.id as \"id\",xsd.xsdh as \"xsdh\",to_char(xsd.date_created,'yyyy-MM-dd') as \"zdrq\"," +
                "NVL(JFMX.GHJE, 0)+NVL(JFMX.TXJE, 0)+NVL(JFMX.DLFJE, 0)+NVL(JFMX.ZJZYF, 0) as \"shje\"," +
                "  gg.code as \"code\" ,gg.name as \"name\",gg.cz as \"cz\", kh.xsxt_xsdh as \"xsxt_xsdh\",kh.carno as \"carno\"," +
                "  kh.bindno as \"bindno\",gg.dw as \"dw\",to_char(rkrq,'yyyy-MM-dd') as \"rkrq\",to_char(ckrq,'yyyy-MM-dd') as \"ckrq\"," +
                "  case sfk.kxsy_sjzdz_id when 121191 then '解锁保证金' when 120591 then  to_char(sfk.sfkrq, 'MM.DD') || '收款(' || sfk.sfje || kxyt.z ||" +
                "  '余额：' || Round(sfk.ye, 2) || ')' else  '其他' end as \"bm\", gg.ggxh as \"ggxh\",  jfmx.zl as \"zl\"," +
                "  jfmx.hsj as \"hsj\", jfmx.sjjsj as \"sjjsj\",nvl(jfmx.ghje,0) as \"ghje\", jfmx.zjfpje as \"zjfpje\",kh.rkjs as \"rkjs\"," +
                "  kh.rksl as \"rksl\",kh.kkdsl as \"sbsl\",nvl(jfmx.zjzyf,0) as \"zjzyf\"," +
                "  jfmx.khfksj as \"khfksj\", jfmx.tzfksj as \"tzfksj\", jfmx.sjzyts as \"sjzyts\", jfmx.jszyts as \"jszyts\"," +
                "  nvl(jfmx.dlfje,0) as \"dlfje\", jfmx.txts as \"txts\",nvl(jfmx.txje,0) as \"txje\",jfmx.shsj as \"shsj\", jfmx.sfjs as \"sfjs\" ";

        String sqlCounter = " select count(1) ";
        String sqlJoinTable = " from t_biz_jfmx jfmx left join t_biz_xsmx xs  on jfmx.xsdmx_id = xs.id left join t_biz_xsd xsd on jfmx.xsd_id = xsd.id " +
                " left join t_biz_kh kh on xs.kh_id = kh.id left join t_sys_gg gg on kh.jtwz_id = gg.id" +
                " left join t_biz_sfk sfk on jfmx.sfk_id = sfk.id  left join t_sys_sjzdz kxyt on sfk.fkfs_sjzdz_id = kxyt.id";

        if (xshtId != null) {
            condition.add(" jfmx.xsht_id =:xshtId ");
            param.put("xshtId", xshtId);
        }
        if (xsdh != null && !xsdh.isEmpty()) {
            condition.add(" xsd.xsdh like :xsdh ");
            param.put("xsdh", "%" + xsdh.trim().toUpperCase() + "%");
        }
        if (xszxh != null && !xszxh.isEmpty()) {
            condition.add("  kh.xsxt_xsdh like :xszxh ");
            param.put("xszxh", "%" + xszxh.trim().toUpperCase() + "%");
        }

        if (carNo != null && !carNo.isEmpty()) {
            condition.add(" kh.carno like :carNo ");
            param.put("carNo", "%" + carNo.trim().toUpperCase() + "%");
        }

        if (bindNo != null && !bindNo.isEmpty()) {
            condition.add(" kh.bindno like :bindNo ");
            param.put("bindNo", "%" + bindNo.trim().toUpperCase() + "%");
        }
        String sqlCondition = " where 1=1" + (condition.isEmpty() ? "" : " and " + StringUtils.join(condition, " and "));
        String orderBy = "order by jfmx.xsd_id desc, jfmx.xsdmx_id desc ";
        SQLQuery query = createSQLQuery(sqlCounter + sqlJoinTable + sqlCondition + orderBy);
        BigDecimal total = (BigDecimal) query.setProperties(param).uniqueResult();
        if (total.intValue() > 0) {
            page.setTotalCount(total.intValue());
            query = createSQLQuery(sqlColumn + sqlJoinTable + sqlCondition + orderBy);
            List<Map> list = query.setFirstResult((pageNo - 1) * pageSize)
                    .setProperties(param)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * 统计计费明细中的贴息
     *
     * @param xsmxId
     * @return
     */
    public List<Map> findSumTxjeFromJfmx(Long xsmxId) {
        String sql = "select nvl(sum(jfmx.txje),0) txx from t_biz_jfmx jfmx where jfmx.xsdmx_id in (select mx.id from t_biz_xsmx mx,t_biz_xsd x,t_biz_ht xsht where mx.xsd_id = x.id and x.xsht_id = xsht.id and xsht.id = (select distinct xsht.id from t_biz_xsmx mx,t_biz_xsd x,t_biz_ht xsht where mx.xsd_id = x.id and x.xsht_id = xsht.id and mx.id = ?))";
        return (List<Map>) this.createSQLQuery(sql, xsmxId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    /**
     * 根据销售单查找分组收付款
     *
     * @param xsdId
     * @return
     */
    public List<Map> GroupBySfkFromJfmx(Long xsdId) {
        String sql = "select j.sfk_id  as \"id\",sum(j.zjfpje) as \"zjfpje\",sum(j.txje) as \"txje\",sum(j.yhje) as \"yhje\" from t_biz_jfmx j where j.xsd_id = ? group by j.sfk_id";
        return (List<Map>) this.createSQLQuery(sql, xsdId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }


    public void changeSfjsByJfmx(Long xsdmxId) {
        String sql = "update t_biz_jfmx set sfjs = 1 where xsdmx_id = ?";
        this.createSQLQuery(sql, xsdmxId).executeUpdate();
    }


    public List<Jfmx> findJfmxByXsdId(Long xsdId) {
        String sql = " from Jfmx where xsd.id = ? ";
        return this.find(sql, xsdId);
    }

    public List<Jfmx> findJfmxBySfkId(Long sfkId) {
        String sql = " from Jfmx where sfk.id = ? ";
        return this.find(sql, sfkId);
    }


    // 查询总共产生的代理费和贴息
    public Map findMapDlfOrTx(Long xshtId) {
        String sql = "select round(nvl(sum(jf.dlfje),0),2) as dlfje ,round(nvl(sum(jf.txje),0),2) as txje from t_biz_jfmx jf " +
                "   where jf.xsht_id = ? ";
        return (Map) this.createSQLQuery(sql, xshtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list().get(0);
    }

    // 查询未付款的代理费
    public Map findMapWfkDlfOrTx(Long xshtId) {
        String sql = "select round(nvl(sum(jf.dlfje),0),2) as dlfje from t_biz_jfmx jf " +
                "   where jf.sffkdlf = 0 and  jf.xsht_id = ? ";
        return (Map) this.createSQLQuery(sql, xshtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list().get(0);
    }

    // 查询当前代理费
    public Map findDqDlfOrTx(Long xsdId) {
        String sql = "select round(nvl(sum(jf.dlfje),0),2) as dlfje from t_biz_jfmx jf " +
                "   where jf.xsd_id = ? ";
        return (Map) this.createSQLQuery(sql, xsdId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list().get(0);
    }

    public List<Map> findSumTxDlf(Long xshtId) {
        String sql = "select round(nvl(sum(jf.dlfje),0),2) as dlf ,round(nvl(sum(jf.txje),0),2) as tx,round(nvl(sum(jf.zjzyf),0),2) as zjzyf from t_biz_jfmx jf " +
                "   where  jf.sfjs = 0 and jf.xsht_id = ? ";
        return (List<Map>) this.createSQLQuery(sql, xshtId).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }

    public List<Jfmx> findJfmxByXshtId(Long xshtId, Long xsjsdId) {
        if (null != xsjsdId) {
            String hql = "from Jfmx where xsht.id = ? and sfjs = 0 and jsd.id = ?";
            return this.find(hql, xshtId, xsjsdId);
        } else {
            String hql = "from Jfmx where xsht.id = ? and sfjs = 0";
            return this.find(hql, xshtId);
        }
    }

    public List<Jfmx> findJfmxByXshtIdXsdId(Xsd xsdItem, boolean bool) {
        String hql;
        if (bool) {
            hql = "from Jfmx where xsht.id = ? and sffkdlf = 0 and dlfje is not null";
            return this.find(hql, xsdItem.getXsht().getId());
        } else {
            hql = "from Jfmx where sffkdlf = 0 and  xsd.id = ? and dlfje is not null";
            return this.find(hql, xsdItem.getId());
        }
    }


    public List<Jfmx> findJfmxCalDLf(Long xsdId) {
        String sql = " from Jfmx where xsd.id = ? and sfk.kxsy != 121191";
        return this.find(sql, xsdId);
    }

    public List<Map> findMapHthk(Xsht xsht) {
        String sql = "select nvl(sum(jf.ghje),0) as hthk  from t_biz_jfmx jf,t_biz_sfk sf " +
                " where jf.sfk_id = sf.id and sf.fkfs_sjzdz_id = 8   and jf.xsht_id = ? " +
                "  union all " +
                "  select nvl(sum(jf.ghje),0) as hthk  from t_biz_jfmx jf,t_biz_sfk sf" +
                "   where jf.sfk_id = sf.id and sf.fkfs_sjzdz_id = 9 and jf.xsht_id = ? ";
        return (List<Map>) this.createSQLQuery(sql, xsht.getId(), xsht.getId()).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE).list();
    }


}
